﻿using CMS.Excep;
using CMS.Model;
using CMS.Utils;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json.Linq;
using System;
using System.Data;
using System.Reflection;
using System.Reflection.Metadata;

namespace CMS.DataAccess
{
    public class DbQuery<T> where T : BaseModel, new()
    {
        DbInstance instance;
        CondCollection whereConds;
        public DbQuery() 
        {
            whereConds = new CondCollection();
            this.instance = ServiceLoader.GetService<DbInstance>();
        }
        public DbQuery<T> OrderBy(params OrderByField[] orderBy)
        {
            if (whereConds == null) whereConds = new CondCollection();
            whereConds.OrderBy.AddRange(orderBy);
            return this;
        }
        public DbQuery<T> Where(CondCollection whereConditionals)
        {
            if (whereConds == null) whereConds = whereConditionals;
            else whereConds.AddWhereCond(whereConditionals);
            return this;
        }
        public List<T> List()
        {
            var sql = instance.ToSelect<T>(whereConds, out SqliteParameter[] parameters);
            var result = new List<T>();
            using (var cmd = instance.MakeCommand(sql, parameters))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        T record = new T();
                        var props = typeof(T).GetProperties();
                        foreach (var prop in props)
                        {
                            var convertedValue = ConvertDbValue(reader[prop.Name], prop.PropertyType);
                            prop.SetValue(record, convertedValue);
                        }
                        result.Add(record);
                    }
                }
            }
            return result;
        }
        public T First()
        {
            var sqlList = instance.ToSelectWithPage<T>(whereConds, 1, 0, out SqliteParameter[] parameters);
            using (var cmd = instance.MakeCommand(sqlList, parameters))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        T record = new T();
                        var props = typeof(T).GetProperties();
                        foreach (var prop in props)
                        {
                            var convertedValue = ConvertDbValue(reader[prop.Name], prop.PropertyType);
                            prop.SetValue(record, convertedValue);
                        }
                        return record;
                    }
                }
            }
            return null;
        }
        public List<T> ToPageList(int index, int pageSize, out int total)
        {
            var sqlCount = instance.ToCount<T>(whereConds, out SqliteParameter[] parameters1);
            var sqlList = instance.ToSelectWithPage<T>(whereConds,pageSize,index, out SqliteParameter[] parameters2);
            using (var cmd1 = instance.MakeCommand(sqlCount, parameters1))
            {
                total = Convert.ToInt32(cmd1.ExecuteScalar()!);
            }
            var result = new List<T>();
            using (var cmd2 = instance.MakeCommand(sqlList, parameters2))
            {
                using (var reader = cmd2.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        T record = new T();
                        var props = typeof(T).GetProperties();
                        foreach (var prop in props)
                        {
                            var convertedValue = ConvertDbValue(reader[prop.Name], prop.PropertyType);
                            prop.SetValue(record, convertedValue);
                        }
                        result.Add(record);
                    }
                }
            }
            return result;
        }
        public T Get(string pk)
        {
            var sql = instance.ToSelect<T>(CondCollection.True().Eq(nameof(BaseModel.Pk),pk), out SqliteParameter[] parameters);
            using (var cmd = instance.MakeCommand(sql, parameters))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        T record = new T();
                        var props = typeof(T).GetProperties();
                        foreach (var prop in props)
                        {
                            var convertedValue = ConvertDbValue(reader[prop.Name], prop.PropertyType);
                            prop.SetValue(record, convertedValue);
                        }
                        return record;
                    }
                }
                throw new MyDataNotFoundException($"无法根据PK={pk}找到对应的{typeof(T)}记录");
            }
        }
        public static object ConvertDbValue(object dbValue, Type targetType)
        {
            if (dbValue is DBNull)
                return targetType.IsValueType ? Activator.CreateInstance(targetType) : null;
            if (dbValue is long l)
            {
                if (targetType == typeof(int))
                    return checked((int)l); // 包含溢出检查
                if (targetType == typeof(short))
                    return checked((short)l);
            }
            if (targetType.IsEnum)
                return Enum.ToObject(targetType, dbValue);
            return Convert.ChangeType(dbValue, targetType);
        }
    }
}
